--    Program: 		CAPAREXT_CUSTOMER_PAYMENT_METHOD.sql
--    Description:	Extract AR Customer Receipt Method information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(20);
   l_set_of_books_id		number;
   l_business_group_id		number;
   
   cursor cur_method (p_business_group_id number) is
      select rcrm.* 
      from   ra_cust_receipt_methods rcrm,
             hz_cust_accounts hca
      where  rcrm.customer_id = hca.cust_account_id
      and    exists (select * 
                     from   chz_parties cp
                     where  cp.s_business_group_id = p_business_group_id
			         and    cp.party_id = hca.party_id);

begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_CUSTOMER_PAYMENT_METHOD.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books short name not found');
       --raise;
   end; 
   
   begin
     select business_group_id
     into   l_business_group_id
     from   hr_organization_units 
     where  upper(name) = 'BG_'||upper(substr(l_ngo_sob, -4));
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': business group not found');
       --raise;
   end;       

   for cur in cur_method(l_business_group_id) loop   	
     
     -- output data
     utl_file.put_line( l_utl_file, 
       cur.CUST_RECEIPT_METHOD_ID  || chr(9) ||
       cur.CUSTOMER_ID             || chr(9) ||
       cur.RECEIPT_METHOD_ID       || chr(9) ||
       cur.PRIMARY_FLAG            || chr(9) ||
       cur.LAST_UPDATE_DATE        || chr(9) ||
       cur.LAST_UPDATED_BY         || chr(9) ||
       cur.CREATION_DATE           || chr(9) ||
       cur.CREATED_BY              || chr(9) ||
       cur.LAST_UPDATE_LOGIN       || chr(9) ||
       cur.REQUEST_ID              || chr(9) ||
       cur.PROGRAM_APPLICATION_ID  || chr(9) ||
       cur.PROGRAM_ID              || chr(9) ||
       cur.PROGRAM_UPDATE_DATE     || chr(9) ||
       cur.SITE_USE_ID             || chr(9) ||
       cur.START_DATE              || chr(9) ||
       cur.END_DATE                || chr(9) ||
       cur.ATTRIBUTE_CATEGORY      || chr(9) ||
       cur.ATTRIBUTE1              || chr(9) ||
       cur.ATTRIBUTE2              || chr(9) ||
       cur.ATTRIBUTE3              || chr(9) ||
       cur.ATTRIBUTE4              || chr(9) ||
       cur.ATTRIBUTE5              || chr(9) ||
       cur.ATTRIBUTE6              || chr(9) ||
       cur.ATTRIBUTE7              || chr(9) ||
       cur.ATTRIBUTE8              || chr(9) ||
       cur.ATTRIBUTE9              || chr(9) ||
       cur.ATTRIBUTE10             || chr(9) ||
       cur.ATTRIBUTE11             || chr(9) ||
       cur.ATTRIBUTE12             || chr(9) ||
       cur.ATTRIBUTE13             || chr(9) ||
       cur.ATTRIBUTE14             || chr(9) ||
       cur.ATTRIBUTE15
	 );
     
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Customer Payment Method not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPAREXT_CUSTOMER_PAYMENT_METHOD', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
